<?php

/**
 * SQL对象化
 */
/**
 * CDbCriteria class file.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @link http://www.yiiframework.com/
 * @copyright 2008-2013 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
/**
 * CDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset.
 *
 * It can be used in AR query methods such as CActiveRecord::find and CActiveRecord::findAll.
 *
 * $criteria=new CDbCriteria();
 * $criteria->compare('status',Post::STATUS_ACTIVE);
 * $criteria->addInCondition('id',array(1,2,3,4,5,6));
 *
 * $posts = Post::model()->findAll($criteria);
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @package system.db.schema
 * @since 1.0
 */

namespace library\Core\Db;

use library\Core\Exception\DbException;
use library\Core\Db\CDbTable;

class CDbCriteriaBuilder {

    const PARAM_PREFIX = ':op';

    /**
     * @var integer the global counter for anonymous binding parameters.
     * This counter is used for generating the name for the anonymous parameters.
     */
    public static $paramCount = 0;
    public $table = '';
    public $tableFrom = '';

    /**
     * @var mixed the columns being selected. This refers to the SELECT clause in an SQL
     * statement. The property can be either a string (column names separated by commas)
     * or an array of column names. Defaults to '*', meaning all columns.
     */
    public $select = '';
    public $insert = '';
    public $update = '';

    /**
     * @var boolean whether to select distinct rows of data only. If this is set true,
     * the SELECT clause would be changed to SELECT DISTINCT.
     */
    public $distinct = false;

    /**
     * @var string query condition. This refers to the WHERE clause in an SQL statement.
     * For example, <code>age>31 AND team=1</code>.
     */
    public $condition = '';

    /**
     * @var array list of query parameter values indexed by parameter placeholders.
     * For example, <code>array(':name'=>'Dan', ':age'=>31)</code>.
     */
    public $params = array();

    /**
     * @var integer maximum number of records to be returned. If less than 0, it means no limit.
     */
    public $limit = -1;

    /**
     * @var integer zero-based offset from where the records are to be returned. If less than 0, it means starting from the beginning.
     */
    public $offset = -1;

    /**
     * @var string how to sort the query results. This refers to the ORDER BY clause in an SQL statement.
     */
    public $order = '';

    /**
     * @var string how to group the query results. This refers to the GROUP BY clause in an SQL statement.
     * For example, <code>'projectID, teamID'</code>.
     */
    public $group = '';

    /**
     * @var string how to join with other tables. This refers to the JOIN clause in an SQL statement.
     * For example, <code>'LEFT JOIN users ON users.id=authorID'</code>.
     */
    public $join = '';

    /**
     * @var string the condition to be applied with GROUP-BY clause.
     * For example, <code>'SUM(revenue)<50000'</code>.
     */
    public $having = '';

    /**
     * @var mixed the relational query criteria. This is used for fetching related objects in eager loading fashion.
     * This property is effective only when the criteria is passed as a parameter to the following methods of CActiveRecord:
     * <ul>
     * <li>{@link CActiveRecord::find()}</li>
     * <li>{@link CActiveRecord::findAll()}</li>
     * <li>{@link CActiveRecord::findByPk()}</li>
     * <li>{@link CActiveRecord::findAllByPk()}</li>
     * <li>{@link CActiveRecord::findByAttributes()}</li>
     * <li>{@link CActiveRecord::findAllByAttributes()}</li>
     * <li>{@link CActiveRecord::count()}</li>
     * </ul>
     * The property value will be used as the parameter to the {@link CActiveRecord::with()} method
     * to perform the eager loading. Please refer to {@link CActiveRecord::with()} on how to specify this parameter.
     * @since 1.1.0
     */
    public $with;

    /**
     * @var string the alias name of the table. If not set, it means the alias is 't'.
     */
    public $alias;

    /**
     * @var boolean whether the foreign tables should be joined with the primary table in a single SQL.
     * This property is only used in relational AR queries for HAS_MANY and MANY_MANY relations.
     *
     * When this property is set true, only a single SQL will be executed for a relational AR query,
     * even if the primary table is limited and the relationship between a foreign table and the primary
     * table is many-to-one.
     *
     * When this property is set false, a SQL statement will be executed for each HAS_MANY relation.
     *
     * When this property is not set, if the primary table is limited or paginated,
     * a SQL statement will be executed for each HAS_MANY relation.
     * Otherwise, a single SQL statement will be executed for all.
     *
     * @since 1.1.4
     */
    public $together;

    /**
     * @var string the name of the AR attribute whose value should be used as index of the query result array.
     * Defaults to null, meaning the result array will be zero-based integers.
     * @since 1.1.5
     */
    public $index;

    /**
     * @var mixed scopes to apply
     *
     * This property is effective only when passing criteria to
     * the one of the following methods:
     * <ul>
     * <li>{@link CActiveRecord::find()}</li>
     * <li>{@link CActiveRecord::findAll()}</li>
     * <li>{@link CActiveRecord::findByPk()}</li>
     * <li>{@link CActiveRecord::findAllByPk()}</li>
     * <li>{@link CActiveRecord::findByAttributes()}</li>
     * <li>{@link CActiveRecord::findAllByAttributes()}</li>
     * <li>{@link CActiveRecord::count()}</li>
     * </ul>
     *
     * Can be set to one of the following:
     * <ul>
     * <li>One scope: $criteria->scopes='scopeName';</li>
     * <li>Multiple scopes: $criteria->scopes=array('scopeName1','scopeName2');</li>
     * <li>Scope with parameters: $criteria->scopes=array('scopeName'=>array($params));</li>
     * <li>Multiple scopes with parameters: $criteria->scopes=array('scopeName1'=>array($params1),'scopeName2'=>array($params2));</li>
     * <li>Multiple scopes with the same name: array(array('scopeName'=>array($params1)),array('scopeName'=>array($params2)));</li>
     * </ul>
     * @since 1.1.7
     */
    public $scopes;
    public $tableClass = NULL;

    /**
     * Constructor.
     * @param array $data criteria initial property values (indexed by property name)
     */
    public function __construct() {
        
    }

    public function from($table, $fields) {
        return $this->select($table, $fields);
    }

    public function select($table, $fields) {

        if (!empty($this->table) || !(is_array($fields) || is_string($fields))) {
            throw new DbException('paramers error!', 400);
        }

        $this->tableClass = $this->checkTableType($table);

        $tableName = $this->tableClass->_TableName_;

        $this->table = $tableName;
        $this->tableFrom = "`{$tableName}`";

        $this->quoteSelectName($tableName, $fields);

        return $this;
    }

    public function insert($table, $data) {

        if (!empty($this->table) || !is_array($data))
            throw new DbException('paramers error!', 400);

        $this->tableClass = $this->checkTableType($table);
        $tableName = $this->tableClass->_TableName_;

        $this->table = $tableName;
        $this->tableFrom = "`{$tableName}`";

        foreach ($data as $name => $value) {
            $this->insert[] = "`{$name}`";
            $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;
        }

        return $this;
    }

    public function update($table, $data) {

        if (!empty($this->table) || !is_array($data))
            throw new DbException('paramers error!', 400);

        $this->tableClass = $this->checkTableType($table);
        $tableName = $this->tableClass->_TableName_;

        $this->table = $tableName;
        $this->tableFrom = "`{$tableName}`";


        foreach ($data as $name => $value) {
            $this->update[] = "`{$this->table}`.`{$name}`=" . self::PARAM_PREFIX . self::$paramCount;
            $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;
        }

        return $this;
    }

    public function delete($table) {

        if (!empty($this->table))
            throw new DbException('paramers error!', 400);

        $this->tableClass = $this->checkTableType($table);
        $tableName = $this->tableClass->_TableName_;

        $this->table = $tableName;
        $this->tableFrom = "`{$tableName}`";

        return $this;
    }

    /**
     * Appends a condition to the existing {@link condition}.
     * The new condition and the existing condition will be concatenated via the specified operator
     * which defaults to 'AND'.
     * The new condition can also be an array. In this case, all elements in the array
     * will be concatenated together via the operator.
     * This method handles the case when the existing condition is empty.
     * After calling this method, the {@link condition} property will be modified.
     * @param mixed $condition the new condition. It can be either a string or an array of strings.
     * @param string $operator the operator to join different conditions. Defaults to 'AND'.
     * @return CDbCriteria the criteria object itself
     */
    public function addCondition($condition, $operator = 'AND') {
        if (is_array($condition)) {
            if ($condition === array())
                return $this;
            $condition = '(' . implode(') ' . $operator . ' (', $condition) . ')';
        }

        if ($this->condition === '')
            $this->condition = $condition;
        else
            $this->condition = '(' . $this->condition . ') ' . $operator . ' (' . $condition . ')';
        return $this;
    }

    /**
     * Appends a search condition to the existing {@link condition}.
     * The search condition and the existing condition will be concatenated via the specified operator
     * which defaults to 'AND'.
     * The search condition is generated using the SQL LIKE operator with the given column name and
     * search keyword.
     * @param string $column the column name (or a valid SQL expression)
     * @param string $keyword the search keyword. This interpretation of the keyword is affected by the next parameter.
     * @param boolean $escape whether the keyword should be escaped if it contains characters % or _.
     * When this parameter is true (default), the special characters % (matches 0 or more characters)
     * and _ (matches a single character) will be escaped, and the keyword will be surrounded with a %
     * character on both ends. When this parameter is false, the keyword will be directly used for
     * matching without any change.
     * @param string $operator the operator used to concatenate the new condition with the existing one.
     * Defaults to 'AND'.
     * @param string $like the LIKE operator. Defaults to 'LIKE'. You may also set this to be 'NOT LIKE'.
     * @return CDbCriteria the criteria object itself
     */
    public function addSearchCondition($column, $keyword, $escape = true, $operator = 'AND', $like = 'LIKE') {
        if ($keyword == '')
            return $this;
        if ($escape)
            $keyword = '%' . strtr($keyword, array('%' => '\%', '_' => '\_', '\\' => '\\\\')) . '%';
        $column = $this->quoteColumnName($column);
        $condition = $column . " $like " . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $keyword;
        return $this->addCondition($condition, $operator);
    }

    /**
     * Appends an IN condition to the existing {@link condition}.
     * The IN condition and the existing condition will be concatenated via the specified operator
     * which defaults to 'AND'.
     * The IN condition is generated by using the SQL IN operator which requires the specified
     * column value to be among the given list of values.
     * @param string $column the column name (or a valid SQL expression)
     * @param array $values list of values that the column value should be in
     * @param string $operator the operator used to concatenate the new condition with the existing one.
     * Defaults to 'AND'.
     * @return CDbCriteria the criteria object itself
     */
    public function addInCondition($column, $values, $operator = 'AND') {
        $table = $this->table;

        if (($n = count($values)) < 1)
            $condition = '0=1'; // 0=1 is used because in MSSQL value alone can't be used in WHERE
        elseif ($n === 1) {
            $value = reset($values);
            if ($value === null)
                $condition = $column . ' IS NULL';
            else {
                $condition = $column . '=' . self::PARAM_PREFIX . self::$paramCount;
                $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;
            }
        } else {
            $params = array();
            foreach ($values as $value) {
                $params[] = self::PARAM_PREFIX . self::$paramCount;
                $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;
            }
            $condition = $column . ' IN (' . implode(', ', $params) . ')';
        }
        return $this->addCondition($condition, $operator);
    }

    /**
     * Appends an NOT IN condition to the existing {@link condition}.
     * The NOT IN condition and the existing condition will be concatenated via the specified operator
     * which defaults to 'AND'.
     * The NOT IN condition is generated by using the SQL NOT IN operator which requires the specified
     * column value to be among the given list of values.
     * @param string $column the column name (or a valid SQL expression)
     * @param array $values list of values that the column value should not be in
     * @param string $operator the operator used to concatenate the new condition with the existing one.
     * Defaults to 'AND'.
     * @return CDbCriteria the criteria object itself
     * @since 1.1.1
     */
    public function addNotInCondition($column, $values, $operator = 'AND') {
        if (($n = count($values)) < 1)
            return $this;

        $column = $this->quoteColumnName($column);

        if ($n === 1) {
            $value = reset($values);
            if ($value === null)
                $condition = $column . ' IS NOT NULL';
            else {
                $condition = $column . '!=' . self::PARAM_PREFIX . self::$paramCount;
                $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;
            }
        } else {
            $params = array();
            foreach ($values as $value) {
                $params[] = self::PARAM_PREFIX . self::$paramCount;
                $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;
            }
            $condition = $column . ' NOT IN (' . implode(', ', $params) . ')';
        }
        return $this->addCondition($condition, $operator);
    }

    /**
     * Appends a condition for matching the given list of column values.
     * The generated condition will be concatenated to the existing {@link condition}
     * via the specified operator which defaults to 'AND'.
     * The condition is generated by matching each column and the corresponding value.
     * @param array $columns list of column names and values to be matched (name=>value)
     * @param string $columnOperator the operator to concatenate multiple column matching condition. Defaults to 'AND'.
     * @param string $operator the operator used to concatenate the new condition with the existing one.
     * Defaults to 'AND'.
     * @return CDbCriteria the criteria object itself
     */
    public function addColumnCondition($columns, $columnOperator = 'AND', $operator = 'AND')
    {
        $params = array();
        foreach ($columns as $name => $value) {
            $name = $this->quoteColumnName($name);
            if ($value === null)
                $params[] = $name . ' IS NULL';
            else {
                $params[] = $name . '=' . self::PARAM_PREFIX .
                    self::$paramCount;
                $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;
            }
        }
        return $this->addCondition(implode(" $columnOperator ", $params), $operator);
    }

    /**
     * Adds a between condition to the {@link condition} property.
     *
     * The new between condition and the existing condition will be concatenated via
     * the specified operator which defaults to 'AND'.
     * If one or both values are empty then the condition is not added to the existing condition.
     * This method handles the case when the existing condition is empty.
     * After calling this method, the {@link condition} property will be modified.
     * @param string $column the name of the column to search between.
     * @param string $valueStart the beginning value to start the between search.
     * @param string $valueEnd the ending value to end the between search.
     * @param string $operator the operator used to concatenate the new condition with the existing one.
     * Defaults to 'AND'.
     * @return CDbCriteria the criteria object itself
     * @since 1.1.2
     */
    public function addBetweenCondition($column, $valueStart, $valueEnd, $operator = 'AND') {
        if ($valueStart === '' || $valueEnd === '')
            return $this;

        $column = $this->quoteColumnName($column);

        $paramStart = self::PARAM_PREFIX . self::$paramCount++;
        $paramEnd = self::PARAM_PREFIX . self::$paramCount++;
        $this->params[$paramStart] = $valueStart;
        $this->params[$paramEnd] = $valueEnd;
        $condition = "( $column BETWEEN $paramStart AND $paramEnd )";

        return $this->addCondition($condition, $operator);
    }

    /**
     * @return array the array representation of the criteria
     */
    public function toArray() {
        $result = array();
        foreach (array('select', 'condition', 'params', 'limit', 'offset', 'order', 'group', 'join', 'having', 'distinct', 'scopes', 'with', 'alias', 'index', 'together') as $name)
            $result[$name] = $this->$name;
        return $result;
    }

    private $_SORT_DESC = 'DESC';
    private $_SORT_ASC = 'ASC';

    /**
     * 小于
     * @param unknown $column
     * @param unknown $value
     * @param string $operator
     */
    public function addLtCondition($column, $value, $operator = 'AND') {
        $column = $this->quoteColumnName($column);

        $condition = $column . '<' . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;

        return $this->addCondition($condition, $operator);
    }

    /**
     * 小于等于
     * @param unknown $column
     * @param unknown $value
     * @param string $operator
     * @return CDbCriteria
     */
    public function addLteCondition($column, $value, $operator = 'AND') {
        $column = $this->quoteColumnName($column);

        $condition = $column . '<=' . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;

        return $this->addCondition($condition, $operator);
    }

    /**
     * 大于
     * @param unknown $column
     * @param unknown $value
     * @param string $operator
     * @return CDbCriteria
     */
    public function addGtCondition($column, $value, $operator = 'AND') {
        $column = $this->quoteColumnName($column);

        $condition = $column . '>' . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;

        return $this->addCondition($condition, $operator);
    }

    /**
     * 大于等于
     * @param unknown $column
     * @param unknown $value
     * @param string $operator
     * @return CDbCriteria
     */
    public function addGteCondition($column, $value, $operator = 'AND') {
        $column = $this->quoteColumnName($column);

        $condition = $column . '>=' . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;

        return $this->addCondition($condition, $operator);
    }

    /**
     * 不等于
     * @param unknown $column
     * @param unknown $value
     * @param string $operator
     * @return CDbCriteria
     */
    public function addNqCondition($column, $value, $operator = 'AND') {
        $column = $this->quoteColumnName($column);

        $condition = $column . '!=' . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;

        return $this->addCondition($condition, $operator);
    }

    /**
     * 等于
     * @param unknown $column
     * @param unknown $value
     * @param string $operator
     * @return CDbCriteria
     */
    public function addEqCondition($column, $value, $operator = 'AND') {
        $column = $this->quoteColumnName($column);

        $condition = $column . '=' . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $value;

        return $this->addCondition($condition, $operator);
    }

    /**
     * 排序
     * @param string $column
     * @param string $sort
     */
    public function addOrderBy($column, $sort = 'ASC') {

        $column = $this->quoteColumnName($column);


        if (empty($this->order)) {
            $this->order = $column . ' ' . $sort;
        } else {
            $this->order .= ', ' . $column . ' ' . $sort;
        }
        return $this;
    }

    /**
     * 升序
     * @param string $column
     */
    public function addOrderAsc($column) {

        return $this->addOrderBy($column, $this->_SORT_ASC);
    }

    /**
     * 降序
     * @param string $column
     */
    public function addOrderDesc($column) {

        return $this->addOrderBy($column, $this->_SORT_DESC);
    }

    /**
     * 排序
     * @param string $column
     * @param string $sort
     */
    public function addGroupBy($column) {

        $column = $this->quoteColumnName($column);

        if (empty($this->group)) {
            $this->group = $column;
        } else {
            $this->group .= ', ' . $column;
        }
        return $this;
    }

    /**
     * 查询以关键字开头
     * @param string $column
     * @param string $keyword
     * @return CDbCriteria
     */
    public function addLikeLCondition($column, $keyword) {
        if ($keyword == '')
            return $this;

        $column = $this->quoteColumnName($column);

        $keyword = strtr($keyword, array('%' => '\%', '_' => '\_', '\\' => '\\\\')) . '%';
        $condition = $column . " $like " . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $keyword;
        return $this->addCondition($condition, $operator);
    }

    /**
     * 查询以关键字结尾
     * @param string $column
     * @param string $keyword
     * @return CDbCriteria|CDbCriteria
     */
    public function addLikeRCondition($column, $keyword) {
        if ($keyword == '')
            return $this;

        $column = $this->quoteColumnName($column);

        $keyword = '%' . strtr($keyword, array('%' => '\%', '_' => '\_', '\\' => '\\\\'));
        $condition = $column . " $like " . self::PARAM_PREFIX . self::$paramCount;
        $this->params[self::PARAM_PREFIX . self::$paramCount++] = $keyword;
        return $this->addCondition($condition, $operator);
    }

    private function quoteColumnName($column) {
        if (!is_string($column))
            return $column;

        if (preg_match('/^\((.)*\)$/', $column))
            return $column;

        if (stristr($column, '.') !== false)
            return $column;

        $column = "`{$this->table}`.`{$column}`";

        return $column;
    }

    private function quoteSelectName($table, $fields) {

        if (is_array($fields)) {
            foreach ($fields as $asWord => $field) {
                $select = $field;
                if (!preg_match('/^\((.)*\)$/', $field)) {
                    $select = "`{$table}`.`{$field}` AS ";
                    $select .= is_numeric($asWord) ? "`{$field}`" : "`{$asWord}`";
                } else {
                    $select .= is_numeric($asWord) ? '' : " AS `{$asWord}`";
                }

                $this->select[] = $select;
            }
        } elseif (is_string($fields)) {
            if (!preg_match('/^\((.)*\)$/', $fields)) {
                $select = "`{$table}`.{$fields}";
            } else {
                $select = $fields;
            }

            $this->select[] = $select;
        }
    }

    public function addSelect($fields, $table = '') {
        if (!is_array($select)) {
            throw DbException('', 400);
        }

        $table = empty($table) ? $this->table : $table;

        $this->quoteSelectName($table, $fields);

        return $this;
    }

    private $_LEFT_JOIN = 'LEFT JOIN';
    private $_RIGHT_JOIN = 'RIGHT JOIN';
    private $_INNER_JOIN = 'INNER JOIN';

    public function addJoin($joinType, $table, $on, $fields = array()) {

        $table = $this->checkTableType($table);
        $tableName = $table->_TableName_;


        $this->quoteSelectName($tableName, $fields);

        $this->tableFrom .= " {$joinType} `{$tableName}` ON {$on}";

        return $this;
    }

    public function addLeftJoin($table, $on, $fields = array()) {

        return $this->addJoin($this->_LEFT_JOIN, $table, $on, $fields);
    }

    public function addRightJoin($table, $on, $fields = array()) {

        return $this->addJoin($this->_RIGHT_JOIN, $table, $on, $fields);
    }

    public function addInnerJoin($table, $on, $fields = array()) {

        return $this->addJoin($this->_INNER_JOIN, $table, $on, $fields);
    }

    public function setLimit($limit) {
        $this->limit = $limit;
        return $this;
    }

    public function setOffset($offset) {
        $this->offset = $offset;
        return $this;
    }

    private function checkTableType($table) {

        if ($table instanceof CDbTable) {

            return $table;
        } else {
            throw new DbException('table type error!', 400);
        }
    }

}
